﻿using shopsoft.dbutility;
using shopsoft.models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace shopsoft.business.SystemConfig
{
    /// <summary>
    /// 成员管理
    /// </summary>
    public class SCMember
    {
        MsSqlHelper msSqlHelper = new MsSqlHelper();

        /// <summary>
        /// 获取会员等级
        /// </summary>
        /// <param name="members"></param>
        /// <returns></returns>
        public List<MemberGrade> GetMemberGrades(MemberGrade where)
        {
            string sqlWhere = " 1=1 ";
            if (where.DelFlag != null)
                sqlWhere += " and State = " + (where.DelFlag == true ? "1" : "0");
            
            try
            {
                DataSet ds;

                msSqlHelper.RunProcedureOrSql("select * from t_Member_Grade where " + sqlWhere, null, "t_Member_Grade", 2, out ds);

                var models = new List<MemberGrade>();

                foreach (DataRow dataRow in ds.Tables["t_Member_Grade"].Rows)
                {
                    var model = new MemberGrade
                    {
                        ID = Convert.ToInt32(dataRow["Member_Grade_ID"]),
                        Name = dataRow["Grade_Name"].ToString(),
                        DelFlag = Convert.ToBoolean(dataRow["State"])
                    };
                    models.Add(model);
                }

                return models;
            }
            catch (OleDbException oleDbException)
            {
                throw new ConnectionException("数据库连接失败", oleDbException);
            }
        }

        /// <summary>
        /// 获取会员信息
        /// </summary>
        /// <param name="members"></param>
        /// <returns></returns>
        public List<Member> GetMemberInfos(Member where)
        {
            string sqlWhere = " 1=1 ";
            //删除标记
            if (where.DelFlag != null)
                sqlWhere += " and a.State = " + (where.DelFlag == true ? "1" : "0");
            //会员号
            if (where.MemberNo != null)
                sqlWhere += " and a.Member_No like '%" + where.MemberNo + "%'";
            //会员名称
            if (where.Name != null)
                sqlWhere += " and a.Member_Name like '%" + where.Name + "%'";
            //会员等级
            if (where.Name != null)
                sqlWhere += " and a.Member_Grade_ID = " + where.MemberGrade.ID.ToString();

            try
            {
                DataSet ds;

                string strSql = @"SELECT a.Member_ID, 
                                        a.Member_No, a.Member_Name, a.Member_Password, a.Sex, a.Tel, 
                                        a.Mobile_No, a.Email, a.Identity_Type, a.Identity_No, a.Validity_Date, a.Reg_Date, 
                                        a.Update_DateTime, a.IS_Share_Member_Info, a.Member_State, a.Member_SID, a.State, a.Comment, 
                                        a.Company_ID, b.System_Show_Name, a.Store_ID, c.Store_Name, a.Member_Grade_ID, d.Grade_Name, 
                                        a.Balance, a.Bouns, a.Leaving_Deal_time, a.Remaining_Time, a.Operator_ID   
                                  FROM t_Member_Info a, t_Company_Info b, t_Store_Info c, t_Member_Grade d
                                  WHERE a.Company_ID = b.Company_ID and a.Company_ID = c.Company_ID and a.Store_ID = c.Store_ID and 
                                        a.Company_ID = d.Company_ID and a.Member_Grade_ID = d.Member_Grade_ID and";
                msSqlHelper.RunProcedureOrSql(strSql + sqlWhere, null, "t_Member_Info", 2, out ds);

                var models = new List<Member>();

                foreach (DataRow dataRow in ds.Tables["t_Member_Info"].Rows)
                {
                    var model = new Member
                    {
                        ID = Convert.ToInt32(dataRow["Member_ID"]),
                        Name = dataRow["Member_Name"].ToString(),
                        Sex = Convert.ToBoolean(dataRow["Sex"]),
                        TelPhone = dataRow["Tel"].ToString(),
                        MobilePhone = dataRow["Mobile_No"].ToString(),
                        EMail = dataRow["Email"].ToString(),
                        IdentityType = Convert.ToInt16(dataRow["Identity_Type"]),
                        IdentityNo = dataRow["Identity_No"].ToString(),
                        ValidityDate = Convert.ToDateTime(dataRow["Validity_Date"]),
                        RegDate = Convert.ToDateTime(dataRow["Reg_Date"]),
                        UpdateDateTime = Convert.ToDateTime(dataRow["Update_DateTime"]),
                        IsShareMemberInfos = Convert.ToInt16(dataRow["IS_Share_Member_Info"]),
                        MemberState = Convert.ToInt16(dataRow["Member_State"]),
                        MemberSID = Convert.ToInt16(dataRow["Member_SID"]),
                        DelFlag = Convert.ToBoolean(dataRow["State"]),
                        Comment = Convert.ToString(dataRow["Comment"]),
                        Company = new Company
                        {
                            ID = Convert.ToInt32(dataRow["Company_ID"]),
                            Name = dataRow["System_Show_Name"].ToString()
                        },
                        Store = new Store
                        {
                            ID = Convert.ToInt32(dataRow["Store_ID"]),
                            Name = dataRow["Store_Name"].ToString()
                        },
                        MemberGrade = new MemberGrade {
                            ID = Convert.ToInt32(dataRow["Member_Grade_ID"]),
                            Name = dataRow["Grade_Name"].ToString()
                        },
                        MemberNo = dataRow["Member_No"].ToString(),
                        MemberPassWord = dataRow["Member_Password"].ToString(),
                        //MemberPhoto = dataRow["Member_Photo"].ToString()
                        MemberBalance = Convert.ToDouble(dataRow["Balance"]),
                        LeavingDealtime = Convert.ToInt16(dataRow["Leaving_Deal_time"]),
                        RemainingTime = Convert.ToInt16(dataRow["Remaining_Time"]),
                        MemberBouns = Convert.ToInt16(dataRow["Bouns"]),
                        OperatorNo = new Users
                        {
                            ID = int.Parse(dataRow["Operator_ID"].ToString())
                        }
                    };
                    models.Add(model);
                }

                return models;
            }
            catch (OleDbException oleDbException)
            {
                throw new ConnectionException("数据库连接失败", oleDbException);
            }
        }
    }
}
